package com.youhp.spire.xls.demo.Charts.ColumnCharts;

import com.spire.xls.*;
import com.spire.xls.charts.*;
import java.awt.*;

public class StackedColumn {

    public static void main(String[] args) {

        executeStackedColumn(true);
    }

    private static void executeStackedColumn(boolean is3D)
    {
        //Create a Workbook
        Workbook workbook = new Workbook();

        //Get the first sheet and set its name
        Worksheet sheet = workbook.getWorksheets().get(0);
        sheet.setName("StackedColumn");

        //Set chart data
        createChartData(sheet);

        //Add a chart
        Chart chart = sheet.getCharts().add();

        //Set region of chart data
        chart.setDataRange(sheet.getCellRange("A1:C5"));
        chart.setSeriesDataFromRange(false);

        //Set position of chart
        chart.setLeftColumn(1);
        chart.setTopRow(6);
        chart.setRightColumn(11);
        chart.setBottomRow(29);

        if (is3D)
        {
            chart.setChartType(ExcelChartType.Column3DStacked);
        }
        else
        {
            chart.setChartType(ExcelChartType.ColumnStacked);
        }

        //Chart title
        chart.setChartTitle("Sales market by country");
        chart.getChartTitleArea().isBold(true);
        chart.getChartTitleArea().setSize(12);

        //Chart Axis
        chart.getPrimaryCategoryAxis().setTitle("Country");
        chart.getPrimaryCategoryAxis().getFont().isBold(true);
        chart.getPrimaryCategoryAxis().getTitleArea().isBold(true);

        chart.getPrimaryValueAxis().setTitle("Sales(in Dollars)");
        chart.getPrimaryValueAxis().hasMajorGridLines(false);
        chart.getPrimaryValueAxis().setMinValue(1000);
        chart.getPrimaryValueAxis().getTitleArea().isBold(true);
        chart.getPrimaryValueAxis().getTitleArea().setTextRotationAngle(90);

        ChartSeries series = chart.getSeries();
        for (int i = 0;i < series.size();i++)
        {
            ChartSerie cs = series.get(i);
            cs.getFormat().getOptions().isVaryColor(true);
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }

        //Chart legend
        chart.getLegend().setPosition(LegendPositionType.Top);

        //Save the result file
        workbook.saveToFile("output/StackedColumn.xlsx",ExcelVersion.Version2010);

    }

    private static void createChartData(Worksheet sheet)
    {
        //Set value of specified cell
        sheet.getCellRange("A1").setValue("Country");
        sheet.getCellRange("A2").setValue("Cuba");
        sheet.getCellRange("A3").setValue("Mexico");
        sheet.getCellRange("A4").setValue("France");
        sheet.getCellRange("A5").setValue("German");

        sheet.getCellRange("B1").setValue("Jun");
        sheet.getCellRange("B2").setNumberValue(6000);
        sheet.getCellRange("B3").setNumberValue(8000);
        sheet.getCellRange("B4").setNumberValue(9000);
        sheet.getCellRange("B5").setNumberValue(8500);

        sheet.getCellRange("C1").setValue("Aug");
        sheet.getCellRange("C2").setNumberValue(3000);
        sheet.getCellRange("C3").setNumberValue(2000);
        sheet.getCellRange("C4").setNumberValue(2300);
        sheet.getCellRange("C5").setNumberValue(4200);

        //Style
        sheet.getCellRange("A1:C1").setRowHeight(15);
        sheet.getCellRange("A1:C1").getCellStyle().setColor(Color.darkGray);
        sheet.getCellRange("A1:C1").getCellStyle().getExcelFont().setColor(Color.white);
        sheet.getCellRange("A1:C1").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
        sheet.getCellRange("A1:C1").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);

        sheet.getCellRange("B2:C5").getCellStyle().setNumberFormat("\"$\"#,##0");
    }
}
